Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

Field lists

All ODBC data sources support field lists. For Informix and Sybase, the DataServer fully supports the use of field lists in queries (DEFINE QUERY, FOR EACH, PRESELECT, and SQL SELECT statements). For example, the following statement returns the same results for an OpenEdge database and an ODBC data source:

DEFINE QUERY myquery FOR customer FIELDS (cust_num name) SCROLLING.

Include the SCROLLING option to enable GET PREV.

You must include the NO–LOCK option when you open queries that are defined with field lists, as the following example shows:

OPEN QUERY myquery FOR EACH customer NO-LOCK WHERE cust_num < 6 BY name.

Similarly, you must include the NO–LOCK option in FOR EACH statements that include field lists, as in the following example:

FOR EACH customer FIELDS (cust_num name) NO-LOCK:

Field lists are effective only when you also specify the NO–LOCK option. This option ensures that the DataServer does not have to refetch rows, which can slow performance.

Use field lists to retrieve only those fields that your application requires. (For performance reasons, the DataServer retrieves the first index field even if you do not include it in the field list. In cases where the DataServer can predict that a query will require a refetch, it retrieves the entire record.) The DataServer allocates memory based on the maximum size defined for a field in a record. Omitting larger fields from a query can enhance performance. In addition, combining lookahead cursors and field lists especially improves a query’s performance.

When you specify a field that has an extent, the query returns the entire array.

When the DataServer processes a query with a field list, it caches the fields that are part of the field list and any other fields that the query specified, which you can then access without making another call to the ODBC. For example, the DataServer fetches the name and the zip field to process the following query:

FOR EACH customer FIELDS (name) WHERE zip = 01730 NO-LOCK:

Note: Cached fields might have performance implications if you modify the record later, as the DataServer must refetch the record to place a lock on it.

If you specify a field list in a join, you might have to adjust the cache size for lookahead cursors, either with the CACHE–SIZE option in a QUERY–TUNING phrase or at the session level with the -Dsrv qt_cache_size startup parameter.

Any performance gained through field lists is lost if you use nonlookahead cursors.

See the Record Phrase entry in the OpenEdge Development: Progress 4GL Reference for more information on the FIELDS option.